using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace NOTIFICATION_SERVICES
{
    public partial class Form1 : Form
    {
        private DataSet meuDataSet = null;   
        private SqlConnection conexao = null;   
        private SqlCommand comando = null;   
        private string strConexao;   

        public Form1()
        {
            InitializeComponent();
        }

        private bool EnoughPermission()
        {
            SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
            try
            {
                perm.Demand();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }
        }

        private void getDados()
        {
            meuDataSet.Clear();

            comando.Notification = null;
            
            SqlDependency dependencia = new SqlDependency(comando);

            dependencia.OnChange += new OnChangeEventHandler(dependencia_OnChange);

            using (SqlDataAdapter adapter = new SqlDataAdapter(comando))
            {
                adapter.Fill(meuDataSet, "pessoa");
                dataGridView1.DataSource = meuDataSet;
                dataGridView1.DataMember = "pessoa";
            }
        }

        delegate void UIDelegate();
        
        private void dependencia_OnChange(object sender, SqlNotificationEventArgs e)
        {
            UIDelegate uidel = new UIDelegate(RefreshData);
            this.Invoke(uidel, null);

            SqlDependency dependencia = (SqlDependency)sender;
            dependencia.OnChange -= dependencia_OnChange;
        }
        
        private void RefreshData()
        {
            getDados();
        } 

        private void button1_Click(object sender, EventArgs e)
        {
            strConexao = "Data Source=NOTEBOOK;Integrated Security=SSPI;Initial Catalog=CAP_09;";
            string strSQL = "SELECT * FROM PESSOA";

            SqlDependency.Stop(strConexao);
            SqlDependency.Start(strConexao);
            
            if (conexao == null)
                conexao = new SqlConnection(strConexao);
            
            if (comando == null)
                comando = new SqlCommand(strSQL, conexao);
            
            if (meuDataSet == null)
                meuDataSet = new DataSet();
            
            getDados();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            SqlDependency.Stop(strConexao);

            if (conexao != null)
                conexao.Close();   
        }
    }
}